﻿Imports System.Configuration
Imports System.Management
Imports System.Data.SqlClient
Public Class Principale
    Private connectionstring As String
    Private myconnection As SqlConnection
    Private avvio As Boolean = True
    Private OldCarrello As Integer
    Public Restart As Boolean = False
    Public AbleRrestart As Boolean = False


    Private Sub ButtonOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonOK.Click

        If Timer1.Enabled = True Then

            ' Aggiornamento avviato

            Timer1.Enabled = False

            AggiornaDati()

            ButtonSETUP.Enabled = True
            Bt_Costo_Orario.Enabled = True
            Bt_Tempi_Lavoro.Enabled = True
            BtSottoprocOdont.Enabled = True



        Else

            'Aggiornamento fermo


            AggiornaDati()


            Timer1.Interval = My.Settings.FreqAggiornamento * 60 * 1000
            Timer1.Enabled = True

            ButtonSETUP.Enabled = False
            Bt_Costo_Orario.Enabled = False
            Bt_Tempi_Lavoro.Enabled = False
            BtSottoprocOdont.Enabled = False

        End If

    End Sub

    Private Sub ButtonEsci_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonEsci.Click
        GridEX1.SaveComponentSettings()
        Application.Exit()
    End Sub



    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim test As Boolean
        Dim DimGrid As New System.Drawing.Size


        Me.Text = Utility.FormattaTesto("Flussi")

        Me.Size = Utility.DimensioniMonitor()

        Me.Location = New System.Drawing.Point(0, 0)


        Utility.GrideExSetup(GridEX1, Me)
        Utility.FCSlaveSetup(GridEX1)

        test = True

        AbleRrestart = False
        Restart = False

        While test
            Try
                test = False
                connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings("FormFlussi.My.MySettings.CM_DBConnectionString").ConnectionString
                myconnection = New SqlConnection(connectionstring)
                myconnection.Open()

                If (Restart = True) Then
                    AbleRrestart = False
                    Application.Restart()
                End If

            Catch ex As Exception
                AbleRrestart = True
                myconnection.Close()
                myconnection.Dispose()
                MsgBox("Database non trovato!")
                Config.ShowDialog()
                test = True
            End Try
        End While

        Try

            Me.DataInizio.Value = Now.Date
            Me.DataFine.Value = DateAdd(DateInterval.Day, 1, Now.Date)
            Me.NrGiorni.Text = 1

            Me.SelOdontotecnicoTableAdapter1.FillBy(Me.CM_DBDataSet1.SelOdontotecnico)

            Me.ImpostaValueListOdontotecnico()

        Catch ex As Exception
            Utility.MessaggioErrore("Errore", ex, "Flussi")
        End Try


    End Sub

    Private Function VerificaPresenzaSlave() As Boolean
        Dim SqlStr As String = "select count(*) from IPPrescrizioniAll"
        Dim SQLcomando As New SqlCommand(SqlStr, myconnection)
        Dim reader As SqlDataReader

        Try
            VerificaPresenzaSlave = True
            reader = SQLcomando.ExecuteReader()
            reader.Read()
            reader.Close()
        Catch ex As Exception

            VerificaPresenzaSlave = False
        End Try

    End Function

    Private Sub ButtonSETUP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSETUP.Click
        Config.ShowDialog()
    End Sub


    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick

        AggiornaDati()

    End Sub

    Private Sub AggiornaDati()
        Dim test As Boolean
        Dim giorni As Integer
        Dim RigaCorrente As Integer



        If myconnection.State = ConnectionState.Open Then
            myconnection.Close()

        End If

        test = True

        While test
            Try
                test = False
                connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings("FormFlussi.My.MySettings.CM_DBConnectionString").ConnectionString
                myconnection = New SqlConnection(connectionstring)
                myconnection.Open()
            Catch ex As Exception
                MsgBox("Database Non Trovato!")
                Config.ShowDialog()
                test = True
            End Try

        End While


        Try
            giorni = Convert.ToInt16(NrGiorni.Text)
        Catch ex As Exception
            MsgBox("Inserire il numero di giorni!")
            NrGiorni.Focus()
            Exit Sub
        End Try




        Try

            Me.SelOdontotecnicoTableAdapter1.FillBy(Me.CM_DBDataSet1.SelOdontotecnico)
            Me.ImpostaValueListOdontotecnico()
            RigaCorrente = GridEX1.Row

            If VerificaPresenzaSlave() = True Then

                GridEX1.SetDataBinding(CM_DBDataSet1, "IPPrescrizioneAll")

                Me.IpPrescrizioneAllTableAdapter1.Fill(Me.CM_DBDataSet1.IPPrescrizioneAll, DataInizio.Value, DataFine.Value)

            Else

                GridEX1.SetDataBinding(CM_DBDataSet1, "IPPrescrizioneSingola")
                Me.IpPrescrizioneSingolaTableAdapter1.Fill(Me.CM_DBDataSet1.IPPrescrizioneSingola, DataInizio.Value, DataFine.Value)
            End If



            TextBoxDataAggiornamento.Text = Convert.ToString(System.DateTime.Now)
        Catch ex As Exception
            Utility.MessaggioErrore("Errore", ex, "Flussi")

        End Try

        GridEX1.Row = RigaCorrente

    End Sub

    Private Sub ButtonAggiorna_Click(sender As System.Object, e As System.EventArgs) Handles ButtonAggiorna.Click
        If Timer1.Enabled Then

            Timer1.Enabled = False
        End If


        AggiornaDati()

        Timer1.Interval = My.Settings.FreqAggiornamento * 60 * 1000
        Timer1.Enabled = True
    End Sub

    Private Sub NrGiorni_Leave(sender As System.Object, e As System.EventArgs) Handles NrGiorni.Leave
        Dim num_giorni As Integer
        Try
            num_giorni = Convert.ToInt16(NrGiorni.Text)
        Catch ex As Exception
            MsgBox("Inserire un numero!")
            NrGiorni.Focus()

        End Try

        If num_giorni >= 0 Then
            DataFine.Value = DateAdd(DateInterval.Day, num_giorni, DataInizio.Value)
        Else
            DataInizio.Value = DateAdd(DateInterval.Day, num_giorni, DataFine.Value)

        End If

        Exit Sub
    End Sub

    Private Sub BtnExpColl_Click(sender As System.Object, e As System.EventArgs) Handles BtnExpColl.Click
        If GridEX1.GroupMode.ToString = "Expanded" Then
            GridEX1.GroupMode = Janus.Windows.GridEX.GroupMode.Collapsed
            GridEX1.CollapseGroups()
        Else
            GridEX1.GroupMode = Janus.Windows.GridEX.GroupMode.Expanded
            GridEX1.ExpandGroups()
        End If
    End Sub



    Private Sub DataInizio_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DataInizio.ValueChanged
        Dim diffgiorni As Integer

        diffgiorni = DateDiff(DateInterval.Day, DataInizio.Value, DataFine.Value)

        NrGiorni.Text = diffgiorni
    End Sub

    Private Sub DataFine_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DataFine.ValueChanged
        Dim diffgiorni As Integer

        diffgiorni = DateDiff(DateInterval.Day, DataInizio.Value, DataFine.Value)

        NrGiorni.Text = diffgiorni
    End Sub

    Private Sub ImpostaValueListOdontotecnico()
        Dim Column As Janus.Windows.GridEX.GridEXColumn = GridEX1.RootTable.Columns("CodOdontotecnico")

        Column.HasValueList = True

        Dim ValueList As Janus.Windows.GridEX.GridEXValueListItemCollection = Column.ValueList

        ValueList.Clear()

        ValueList.PopulateValueList(Me.CM_DBDataSet1.SelOdontotecnico, "CodOdontotecnico", "Odontotecnico")
        Column.EditType = Janus.Windows.GridEX.EditType.DropDownList
        Column.CompareTarget = Janus.Windows.GridEX.ColumnCompareTarget.Text
        Column.DefaultGroupInterval = Janus.Windows.GridEX.GroupInterval.Text

    End Sub

    Private Sub GridEX1_CurrentCellChanged(sender As System.Object, e As System.EventArgs) Handles GridEX1.CurrentCellChanged

        Try
            Dim Column As Janus.Windows.GridEX.GridEXColumn = GridEX1.CurrentColumn

            Dim CodiceSottoprocedura As Integer
            Dim CodProcedura As Integer
            Dim CodRaggruppamento As Integer

            If Not IsNothing(Column) Then

                Column = GridEX1.CurrentColumn
                If Column.Key = "CodOdontotecnico" Then

                    CodiceSottoprocedura = GridEX1.CurrentRow.Cells.Item("CodSottoprocedura").Value
                    CodProcedura = GridEX1.CurrentRow.Cells.Item("CodProcedura").Value
                    CodRaggruppamento = GridEX1.CurrentRow.Cells.Item("CodRaggruppamento").Value
                    Me.SelOdontotecnicoTableAdapter1.Fill(Me.CM_DBDataSet1.SelOdontotecnico, CodiceSottoprocedura, CodProcedura, CodRaggruppamento)

                    Me.ImpostaValueListOdontotecnico()

                End If

                If Column.Key = "Carrello" Then
                    OldCarrello = GridEX1.CurrentRow.Cells.Item("Carrello").Value
                End If

            End If
        Catch ex As Exception
            If avvio = True Then
                avvio = False
            Else
                Utility.MessaggioErrore("Errore nell'aggiornamento della lista:", ex, "Flussi")
            End If
        End Try
    End Sub

    Private Sub GridEX1_CellEdited(sender As System.Object, e As Janus.Windows.GridEX.ColumnActionEventArgs) Handles GridEX1.CellEdited

        Try

            Dim Column As Janus.Windows.GridEX.GridEXColumn = GridEX1.CurrentColumn
            Dim SqlStr As String
            Dim SQLcomando As SqlCommand
            Dim reader As SqlDataReader
            Dim Numero As Integer = 0
            Dim NumeroCarrelloInizio As Integer = Convert.ToInt32(My.Settings.NumeroCarrelloInizio)
            Dim NumeroCarrelloFine As Integer = Convert.ToInt32(My.Settings.NumeroCarrelloFine)
            Dim Carrello As Integer = GridEX1.CurrentRow.Cells.Item("Carrello").Value


            If Not IsNothing(Column) Then


                If Column.Key = "Carrello" And Carrello <> OldCarrello Then


                    If Carrello < NumeroCarrelloInizio Or Carrello > NumeroCarrelloFine Then
                        MsgBox("Il numero di carrello deve essere compreso tra " & Convert.ToString(NumeroCarrelloInizio) & _
                               " e " & Convert.ToString(NumeroCarrelloFine))
                        GridEX1.CurrentRow.Cells.Item("Carrello").Value = OldCarrello
                        Exit Sub

                    End If

                    SqlStr = "select count(*) as numero from T_PRESC_DETTAGLIO a inner join T_PRESCRIZIONE b " & _
                        "on a.CodPrescrizione=b.CodPrescrizione where a.Numero<>@Prescrizione and a.NumCarrello=@Carrello and b.Consegna>=@DataConsegna "
                    SQLcomando = New SqlCommand(SqlStr, myconnection)
                    SQLcomando.Parameters.AddWithValue("@Prescrizione", GridEX1.CurrentRow.Cells.Item("Prescrizione").Value)
                    SQLcomando.Parameters.AddWithValue("@Carrello", GridEX1.CurrentRow.Cells.Item("Carrello").Value)
                    SQLcomando.Parameters.AddWithValue("@DataConsegna", GridEX1.CurrentRow.Cells.Item("Consegna").Value)
                    reader = SQLcomando.ExecuteReader()
                    reader.Read()
                    Numero = reader.Item("numero")
                    reader.Close()


                    If VerificaPresenzaSlave() = True Then
                        SqlStr = "select count(*) as numero from CM_DB_SST_SLAVE.dbo.T_PRESC_DETTAGLIO a inner join CM_DB_SST_SLAVE.dbo.T_PRESCRIZIONE b " & _
                            "on a.CodPrescrizione=b.CodPrescrizione where a.Numero<>@Prescrizione and a.NumCarrello=@Carrello and b.Consegna>=@DataConsegna "
                        SQLcomando = New SqlCommand(SqlStr, myconnection)
                        SQLcomando.Parameters.AddWithValue("@Prescrizione", GridEX1.CurrentRow.Cells.Item("Prescrizione").Value)
                        SQLcomando.Parameters.AddWithValue("@Carrello", GridEX1.CurrentRow.Cells.Item("Carrello").Value)
                        SQLcomando.Parameters.AddWithValue("@DataConsegna", GridEX1.CurrentRow.Cells.Item("Consegna").Value)
                        reader = SQLcomando.ExecuteReader()
                        reader.Read()
                        Numero = Numero + reader.Item("numero")
                        reader.Close()

                    End If

                    If Numero > 0 Then
                        MsgBox("Numero Carrello già assegnato ad una prescrizione")
                        GridEX1.CurrentRow.Cells.Item("Carrello").Value = OldCarrello
                    End If
                End If
            End If
        Catch ex As Exception
            If avvio = True Then
                avvio = False
            Else
                Utility.MessaggioErrore("Errore nell'aggiornamento del Numero di Carrello:", ex, "Flussi")
            End If

        End Try
    End Sub

    Private Sub SalvaModifiche(Campo As String)
        Dim changedRecords As System.Data.DataTable
        Dim SqlStr As String = ""
        Dim SQLcomando As SqlCommand
        Dim RecordModificati As Integer = 0
        Dim test As Boolean = True
        Dim message As String

        If Campo <> "Carrello" And Campo <> "Odontotecnico" Then
            MsgBox("Si possono modificare solo i campi 'Carrello' e 'Odontotecnico'")
            Exit Sub

        End If

        If VerificaPresenzaSlave() = True Then
            changedRecords = CM_DBDataSet1.IPPrescrizioneAll.GetChanges()
        Else
            changedRecords = CM_DBDataSet1.IPPrescrizioneSingola.GetChanges()
        End If


        If Not (changedRecords Is Nothing) AndAlso _
            (changedRecords.Rows.Count > 0) Then

            If My.Settings.Debug = True Then


                message = "Vuoi salvare le modifiche sul campo " & Campo & " ?"

                If (MessageBox.Show(message, "Flussi", MessageBoxButtons.YesNo, _
                        MessageBoxIcon.Question) = System.Windows.Forms.DialogResult.No) Then
                    test = False
                End If
            End If

            If test = True Then

                Try
                    For x As Int16 = 0 To changedRecords.Rows.Count - 1
                        If changedRecords.Rows(x).Item("db") = "MASTER" Then

                            If Campo = "Carrello" Then
                                SqlStr = "update T_PRESC_DETTAGLIO set NumCarrello=@Carrello " & _
                                    "where Numero=@Prescrizione"
                            End If

                            If Campo = "Odontotecnico" Then
                                SqlStr = "update T_PRESC_DETTAGLIO set CodOdontotecnico=@Odontotecnico " & _
                                    "where Codice=@CodPrescDettaglio"
                            End If

                        Else
                            If Campo = "Carrello" Then
                                SqlStr = "update CM_DB_SST_SLAVE.dbo.T_PRESC_DETTAGLIO set NumCarrello=@Carrello " & _
                                    "where Numero=@Prescrizione"
                            End If

                            If Campo = "Odontotecnico" Then
                                SqlStr = "update CM_DB_SST_SLAVE.dbo.T_PRESC_DETTAGLIO set CodOdontotecnico=@Odontotecnico " & _
                                    "where Codice=@CodPrescDettaglio"
                            End If

                        End If

                        SQLcomando = New SqlCommand(SqlStr, myconnection)
                        If Campo = "Carrello" Then
                            SQLcomando.Parameters.AddWithValue("@Carrello", changedRecords.Rows(x).Item("Carrello"))

                            SQLcomando.Parameters.AddWithValue("@Prescrizione", changedRecords.Rows(x).Item("Prescrizione"))
                        End If
                        If Campo = "Odontotecnico" Then
                            SQLcomando.Parameters.AddWithValue("@Odontotecnico", changedRecords.Rows(x).Item("CodOdontotecnico"))

                            SQLcomando.Parameters.AddWithValue("@CodPrescDettaglio", changedRecords.Rows(x).Item("CodPresDettaglio"))
                        End If

                        RecordModificati = RecordModificati + SQLcomando.ExecuteNonQuery()

                    Next

                    If My.Settings.Debug = True Then


                        message = String.Format("Sono stati modificati {0} record(s).", RecordModificati)
                        MessageBox.Show(message, "Flussi", MessageBoxButtons.OK)
                    End If

                Catch exc As Exception
                    Utility.MessaggioErrore("Si è verificato un errore nell'aggiornamento del db", exc, "Flussi")
                End Try
            End If

        End If
    End Sub

    Private Sub Bt_Costo_Orario_Click(sender As System.Object, e As System.EventArgs) Handles Bt_Costo_Orario.Click
        CostoOrario.Show()

    End Sub

    Private Sub Bt_Tempi_Lavoro_Click(sender As System.Object, e As System.EventArgs) Handles Bt_Tempi_Lavoro.Click
        TempiLavoro.Show()
    End Sub

    Private Sub ContextMenuStrip1_ItemClicked(sender As System.Object, e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ContextMenuStrip1.ItemClicked
        If e.ClickedItem.Name = "Eseguito" Then

            Marca_Eseguito()

        End If

        If e.ClickedItem.Name = "NonEseguito" Then

            Marca_NonEseguito()

        End If

        If e.ClickedItem.Name = "ModificaDati" Then

            ModificaDatiSottoprocedura()

        End If

        AggiornaDati()
    End Sub

    Private Sub GridEX1_MouseDown(sender As System.Object, e As System.Windows.Forms.MouseEventArgs)
        If MouseButtons = Windows.Forms.MouseButtons.Right Then
            ContextMenuStrip1.Show(MousePosition.X, MousePosition.Y)
        End If
    End Sub

    Private Sub GridEX1_RecordUpdated(sender As System.Object, e As System.EventArgs) Handles GridEX1.RecordUpdated
        Dim RigaCorrente As Integer = GridEX1.Row

        SalvaModifiche("Carrello")
        SalvaModifiche("Odontotecnico")

        AggiornaDati()

        GridEX1.Row = RigaCorrente
    End Sub

    Private Sub BtSottoprocOdont_Click(sender As System.Object, e As System.EventArgs) Handles BtSottoprocOdont.Click
        SottoprocedureOdontotecnici.Show()
    End Sub

    Private Sub Marca_Eseguito()

        Dim Righe As Janus.Windows.GridEX.GridEXSelectedItemCollection
        Dim x As Integer
        Dim Riga As Janus.Windows.GridEX.GridEXRow
        Dim SqlComando As SqlCommand
        Dim db As String
        Dim Eseguito As Boolean
        Dim Sql As String
        Dim message As String

        Try

            Righe = GridEX1.SelectedItems
            If My.Settings.Debug = True Then


                message = String.Format("Sono stati selezionati {0} record(s).", Righe.Count)
                MessageBox.Show(message, "Flussi", MessageBoxButtons.OK)
            End If
            For x = 0 To Righe.Count - 1
                Riga = Righe.Item(x).GetRow()
                Eseguito = Riga.Cells.Item("Eseguito").Value
                db = Riga.Cells.Item("db").Value

                If Eseguito = False Then
                    If db = "MASTER" Then
                        Sql = "update T_TRATTAMENTI set DataEsecuzione=getdate() from T_TRATTAMENTI a inner join T_PRESC_DETTAGLIO b " & _
                            " on a.NTrattamento=b.Num_Trat where b.Codice=@Codice"
                    Else
                        Sql = "update CM_DB_SST_SLAVE.dbo.T_TRATTAMENTI set DataEsecuzione=getdate() from CM_DB_SST_SLAVE.dbo.T_TRATTAMENTI a inner join " & _
                            "CM_DB_SST_SLAVE.dbo.T_PRESC_DETTAGLIO b on a.NTrattamento=b.Num_Trat where b.Codice=@Codice"
                    End If

                    If My.Settings.Debug = True Then


                        message = String.Format(Sql)
                        MessageBox.Show(message, "Flussi", MessageBoxButtons.OK)
                    End If

                    SqlComando = New SqlCommand(Sql, myconnection)
                    SqlComando.Parameters.AddWithValue("@Codice", Riga.Cells.Item("CodPresDettaglio").Value)
                    SqlComando.ExecuteNonQuery()

                End If


            Next

        Catch exc As Exception
            Utility.MessaggioErrore("Si è verificato un errore nell'aggiornamento del db", exc, "Flussi")
        End Try
    End Sub

    Private Sub Marca_NonEseguito()

        Dim Righe As Janus.Windows.GridEX.GridEXSelectedItemCollection
        Dim x As Integer
        Dim Riga As Janus.Windows.GridEX.GridEXRow
        Dim SqlComando As SqlCommand
        Dim db As String
        Dim Eseguito As Boolean
        Dim Sql As String

        Try



            Righe = GridEX1.SelectedItems
            For x = 0 To Righe.Count - 1
                Riga = Righe.Item(x).GetRow()
                Eseguito = Riga.Cells.Item("Eseguito").Value
                db = Riga.Cells.Item("db").Value

                If Eseguito = True Then
                    If db = "MASTER" Then
                        Sql = "update T_TRATTAMENTI set DataEsecuzione=NULL from T_TRATTAMENTI a inner join T_PRESC_DETTAGLIO b " & _
                            " on a.NTrattamento=b.Num_Trat where b.Codice=@Codice"
                    Else
                        Sql = "update CM_DB_SST_SLAVE.dbo.T_TRATTAMENTI set DataEsecuzione=NULL from CM_DB_SST_SLAVE.dbo.T_TRATTAMENTI a inner join " & _
                            "CM_DB_SST_SLAVE.dbo.T_PRESC_DETTAGLIO b on a.NTrattamento=b.Num_Trat where b.Codice=@Codice"
                    End If

                    SqlComando = New SqlCommand(Sql, myconnection)
                    SqlComando.Parameters.AddWithValue("@Codice", Riga.Cells.Item("CodPresDettaglio").Value)
                    SqlComando.ExecuteNonQuery()

                End If


            Next

        Catch exc As Exception
            Utility.MessaggioErrore("Si è verificato un errore nell'aggiornamento del db", exc, "Flussi")
        End Try
    End Sub

    Private Sub ModificaDatiSottoprocedura()
        Dim Riga As Janus.Windows.GridEX.GridEXRow

        Riga = GridEX1.GetRow()

        DatiSottoprocedura.CodLaboratorio = Riga.Cells.Item("CodOdontotecnico").Value
        DatiSottoprocedura.CodSottoprocedura = Riga.Cells.Item("CodSottoprocedura").Value

        DatiSottoprocedura.ShowDialog()


    End Sub
End Class